package com.xbniao.gcfs.common.util.ExportExcel;

import com.xbniao.gcfs.common.enums.GcfsEbayYesOrNoStateEnum;
import com.xbniao.gcfs.common.request.model.GcfsEbayCustomOrderModel;
import com.xbniao.gcfs.common.request.model.GcfsEbayCustomOrderProductModel;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author:wangbin.
 * @Description.   报关订单导出预录凭单信息
 * @Date:Created in 13:36 2018/4/27.
 * @Modified By:
 */
public class ExportPreRecordedVoucher extends ExportExcelBase{

    // 测试导出
    public static void main(String[] args) {
        ExportPreRecordedVoucher voucher = new ExportPreRecordedVoucher();
        GcfsEbayCustomOrderModel model = new GcfsEbayCustomOrderModel();
        model.setCustomOrdersCode("12346578946");
        model.setExportPort("天津港");
        model.setTransport("1 空运");
        model.setTransportToolName("787飞机");
        model.setVoyage("20180428/001");
        model.setSalveWaybillCode("9852136479");
        model.setCurrency("501 英镑");
        model.setSupervision("0110 一般贸易");
        model.setExemptionNature("101 一般征税");
        model.setSpecialRelationshipState((short)1);
        model.setTradingCountry("501 英国");
        model.setArrivedCountry("501 英国");
        model.setFingerPort("3153 伦敦矶");
        model.setDomesticSource("44079 江门");
        model.setPriceEffectState((short)2);
        model.setLicenseNo("1111-11-1112");
        model.setDealType("3 FOB");
        model.setPaymentConcessionState((short)1);
        model.setContractNo("XHT18CC");
        model.setTotalNum(123);
        model.setPackageType("2 纸箱");
        model.setTotalGrossWeight(new BigDecimal(5131.56));
        model.setTotalNetWeight(new BigDecimal(4564.36));
        model.setContainerNo("45");
        model.setContainerQuantity(56);
        model.setContainerStandardQuantity(89);
        model.setAttachedDocumentNo("A123,B565");
        model.setAttachedDocumentCode("789,5265");
        model.setDestinationCountry("美国");

        List<GcfsEbayCustomOrderProductModel> productModels = new ArrayList<>();
        GcfsEbayCustomOrderProductModel product = new GcfsEbayCustomOrderProductModel();
        product.setProductCode("8451315");
        product.setCustomNameChinese("中文报关名称");
        product.setDeclarationElement("12|35|衣服|黄色|xl");
        product.setDealNumber(12);
        product.setDealMeasurementUnit("件");
        product.setFirstMeteredQuantity(new BigDecimal(12));
        product.setFirstMeteredUnit("件");
        product.setSecondMeteredQuantity(new BigDecimal(13));
        product.setSecondMeteredUnit("KG");
        product.setGrossWeight(new BigDecimal(21.35));
        product.setNetWeight(new BigDecimal(56.23));
        product.setTotalApplyPrice(new BigDecimal(456789.23));
        product.setReducingTaxExemptions("一般");
        product.setBrand("addiass");

        productModels.add(product);
        model.setGcfsEbayCustomOrderProductModels(productModels) ;
        voucher.export("F:\\test\\gcfs_ebay_custom_excel_export\\预录凭单导出模板_20180427.xls","F:\\test\\gcfs_ebay_custom_excel_export\\预录凭单导出-test.xls",model);
    }

    /**
     * 读取一个 Workbook
     * @param wb
     * @throws Exception
     */
    public void read(Workbook wb,Object data) throws Exception{
        GcfsEbayCustomOrderModel model = (GcfsEbayCustomOrderModel) data;
        // 处理第一个sheet
        processSheet1(wb.getSheetAt(0),model);
    }

    private void processSheet1(Sheet sheet, GcfsEbayCustomOrderModel data)
    {
        // 处理非产品数据
        dealBaseInfo(sheet,data);

        // 检测是否需要插入行，如果需要，插入需要的行数
        checkInsertRow(data.getGcfsEbayCustomOrderProductModels().size(),sheet);

        // 插入报关订单产品数据
        insertProductData(data.getDestinationCountry(),data.getDestinationCountryId(),data.getGcfsEbayCustomOrderProductModels(),sheet);
    }

    /**
     * 处理基本信息，往表格中填入数据
     * @param sheet
     * @param data
     */
    private void dealBaseInfo(Sheet sheet, GcfsEbayCustomOrderModel data) {
        // 第一行   设置预录入编码
        POIExportExcelUtil.updateCellValue(sheet,1,1,data.getCustomOrdersCode());

        // 第四行   设置出口口岸
        POIExportExcelUtil.updateCellValue(sheet,3,5,data.getExportPortId() + " " + data.getExportPort());

        // 第六行   设置运输方式/运输工具名称/航次/提运单号.订舱单号/币制
        POIExportExcelUtil.updateCellValue(sheet,5,5,data.getTransportId() + " " + data.getTransport());
        POIExportExcelUtil.updateCellValue(sheet,5,7,data.getTransportToolName());
        POIExportExcelUtil.updateCellValue(sheet,5,9,data.getVoyage());
        if(!StringUtils.isEmpty(data.getSalveWaybillCode()))
            POIExportExcelUtil.updateCellValue(sheet,5,10,data.getSalveWaybillCode());
        else
            POIExportExcelUtil.updateCellValue(sheet,5,10,data.getMasterWaybillCode());
        POIExportExcelUtil.updateCellValue(sheet,5,15,data.getCurrencyId() + " " + data.getCurrency());

        // 第八行   设置监管方式/征免性质/特殊关系确认
        POIExportExcelUtil.updateCellValue(sheet,7,5,data.getSupervisionId() + " " + data.getSupervision());
        POIExportExcelUtil.updateCellValue(sheet,7,7,data.getExemptionNatureId() + " " + data.getExemptionNature());
        if(data.getSpecialRelationshipState() != null && data.getSpecialRelationshipState().shortValue() == GcfsEbayYesOrNoStateEnum.YES.getCode())
            POIExportExcelUtil.updateCellValue(sheet,7,15,GcfsEbayYesOrNoStateEnum.YES.getName());
        else
            POIExportExcelUtil.updateCellValue(sheet,7,15,GcfsEbayYesOrNoStateEnum.NO.getName());

        // 第十行   设置贸易国别(地区)/运抵国(地区)/指运港/境内货源地/价格影响确认
        POIExportExcelUtil.updateCellValue(sheet,9,0,data.getTradingCountryId() + " " + data.getTradingCountry());
        POIExportExcelUtil.updateCellValue(sheet,9,4,data.getArrivedCountryId() + " " + data.getArrivedCountry());
        POIExportExcelUtil.updateCellValue(sheet,9,8,data.getFingerPortId() + " " + data.getFingerPort());
        POIExportExcelUtil.updateCellValue(sheet,9,11,data.getDomesticSourceId() + " " + data.getDomesticSource());
        if(data.getPriceEffectState() != null && data.getPriceEffectState().shortValue() == GcfsEbayYesOrNoStateEnum.YES.getCode())
            POIExportExcelUtil.updateCellValue(sheet,9,15,GcfsEbayYesOrNoStateEnum.YES.getName());
        else
            POIExportExcelUtil.updateCellValue(sheet,9,15,GcfsEbayYesOrNoStateEnum.NO.getName());

        // 第十二行 设置许可证号/成交方式/运费/与货物有关的特许权使用费支付确认
        POIExportExcelUtil.updateCellValue(sheet,11,0,data.getLicenseNo());
        POIExportExcelUtil.updateCellValue(sheet,11,4,data.getDealTypeId() + " " + data.getDealType());
        if(data.getPaymentConcessionState() != null && data.getPaymentConcessionState().shortValue() == GcfsEbayYesOrNoStateEnum.YES.getCode())
            POIExportExcelUtil.updateCellValue(sheet,11,15,GcfsEbayYesOrNoStateEnum.YES.getName());
        else
            POIExportExcelUtil.updateCellValue(sheet,11,15,GcfsEbayYesOrNoStateEnum.NO.getName());

        // 第十四行 设置合同协议号/件数/包装种类/毛重(公斤)/净重(公斤)
        POIExportExcelUtil.updateCellValue(sheet,13,0,data.getContractNo());
        POIExportExcelUtil.updateCellValue(sheet,13,4,data.getTotalNum());
        POIExportExcelUtil.updateCellValue(sheet,13,6,data.getPackageTypeId() + " " + data.getPackageType());
        POIExportExcelUtil.updateCellValue(sheet,13,9,data.getTotalGrossWeight());
        POIExportExcelUtil.updateCellValue(sheet,13,12,data.getTotalNetWeight());

        // 第十六行 设置集装箱号/随附单证   随附单证要经过处理
        POIExportExcelUtil.updateCellValue(sheet,15,0,data.getContainerNo() + "*" + data.getContainerQuantity() + "（" + data.getContainerStandardQuantity() + "）");
        POIExportExcelUtil.updateCellValue(sheet,15,4,dealDocumentsAttached(data));

        // 第十八行 设置标记唛码及备注
        POIExportExcelUtil.updateCellValue(sheet,17,0,"备注：" + POIExportExcelUtil.showStringInfo(data.getRemark()));
    }



    /**
     * 处理 随附单证 信息
     * @param data
     * @return
     */
    private String dealDocumentsAttached(GcfsEbayCustomOrderModel data) {
        StringBuffer sb = new StringBuffer("");
        String documentNo = data.getAttachedDocumentNo();
        String documentCode = data.getAttachedDocumentCode();
        // 如果随附单证代码 不为空
        if(!StringUtils.isEmpty(documentNo))
        {
            // 如果随附单证代码 包含“,”
            if(documentNo.contains(","))
            {
                String[] documentNos = documentNo.split(",");
                // 随附单证编号不为空
                if(!StringUtils.isEmpty(documentCode))
                {
                    // 随附单证编号 包含“,”
                    if(documentCode.contains(","))
                    {
                        String[] documentCodes = documentCode.split(",");
                        // 如果如果随附单证代码 比 随附单证编号多
                        if(documentNos.length >= documentCodes.length)
                        {
                            for (int i = 0 ; i < documentNos.length ; i++)
                            {
                                if((i+1) > documentCodes.length)
                                    sb.append(documentNos[i] + ": ,");
                                else
                                    sb.append(documentNos[i] + ":" + documentCodes[i] + ",");
                            }
                        }
                        // 如果如果随附单证代码 比 随附单证编号少
                        else
                        {
                            for(int i = 0 ; i < documentCodes.length ; i++)
                            {
                                if((i+1) > documentNos.length)
                                    sb.append(":" + documentCodes[i] + ",");
                                else
                                    sb.append(documentNos[i] + ":" + documentCodes[i] + ",");
                            }
                        }
                    }
                    // 随附单证编号 不包含“,”
                    else
                    {
                        sb.append(documentNos[0] + ":" + documentCode + ",");
                        for(int i = 1 ;i < documentNos.length ; i++)
                            sb.append(documentNos[i] + ": ," );
                    }
                }
                // 随附单证编号 为空
                else
                {
                    for (String no : documentNos)
                        sb.append(no + ": ,");
                }
            }
            // 如果随附单证代码 不包含“,”
            else
            {
                // 随附单证编号不为空
                if(!StringUtils.isEmpty(documentCode))
                {
                    // 随附单证编号 包含“,”
                    if(documentCode.contains(","))
                    {
                        String[] documentCodes = documentCode.split(",");
                        sb.append(documentNo + ":" + documentCodes[0] + ",");
                        for(int i = 1; i < documentCodes.length ; i++)
                            sb.append(" :" + documentCodes[i] + ",");
                    }
                    // 随附单证编号 不包含“,”
                    else
                        sb.append(documentNo + ":" + documentCode + ",");
                }
                // 随附单证编号为空
                else
                    sb.append(documentNo + ": ,");
            }
        }
        // 如果随附单证代码 为空
        else
        {
            // 随附单证编号不为空
            if(!StringUtils.isEmpty(documentCode))
            {
                // 随附单证编号 包含“,”
                if(documentCode.contains(","))
                {
                    String[] documentCodes = documentCode.split(",");
                    for (String code : documentCodes)
                        sb.append(" :" + code + ",");
                }
                // 随附单证编号 不包含“,”
                else
                    sb.append(" :" + documentCode + ",");
            }
        }

        if(sb.toString().length() > 0)
            return sb.toString().substring(0,sb.toString().length()-1);
        else
            return "";
    }

    /**
     * 检测是否需要插入行，如果需要，插入需要的行数
     * @param size
     * @param sheet
     */
    private void checkInsertRow(int size,Sheet sheet) {
        // 如果大于50个产品，则需要插入excel行
        if(size > 50)
        {
            // 需要插入的行数
            int insertCount = size - 50;
            // 获取第68行的行高
            short row_height = POIExportExcelUtil.getRowHeight(sheet,68);
            for(int i = 0 ; i < insertCount ; i++)
                POIExportExcelUtil.copyRows(68+i,68+i,68+i,sheet);
            // 设置最后一行的行高
            POIExportExcelUtil.setRowHeight(sheet,68+insertCount,row_height);
        }
    }

    /**
     * 插入报关订单产品数据
     * @param descrtionCountry
     * @param gcfsEbayCustomOrderProductModels
     * @param sheet
     */
    private void insertProductData(String descrtionCountry,String destinationCountryId,
            List<GcfsEbayCustomOrderProductModel> gcfsEbayCustomOrderProductModels,
                                   Sheet sheet) {
        int rowLine = 19;
        for(int i = 0 ; i < gcfsEbayCustomOrderProductModels.size() ;i ++)
        {
            GcfsEbayCustomOrderProductModel model = gcfsEbayCustomOrderProductModels.get(i);
            if(model == null)
                continue;
            // 处理报关订单产品数据
            dealCustomProductModel(sheet,rowLine,gcfsEbayCustomOrderProductModels.get(i),i,descrtionCountry,destinationCountryId);
        }
    }

    /**
     * 处理报关订单产品数据
     * @param sheet
     * @param rowLine
     * @param model
     * @param i
     */
    private void dealCustomProductModel(Sheet sheet, int rowLine, GcfsEbayCustomOrderProductModel model, int i,String descrtionCountry,String destinationCountryId) {
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,0,i+1);
//        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,1,model.getProductCode());
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,1,model.getHsCode());
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,2,model.getCustomNameChinese());
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,3,model.getDeclarationElement());
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,4,model.getDealNumber() + " " + POIExportExcelUtil.showStringInfo(model.getDealMeasurementUnit()));
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,5,model.getFirstMeteredQuantity() + " " + POIExportExcelUtil.showStringInfo(model.getFirstMeteredUnit()));
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,6,model.getSecondMeteredQuantity() + " " + POIExportExcelUtil.showStringInfo(model.getSecondMeteredUnit()));
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,8,model.getGrossWeight());
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,9,model.getNetWeight());
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,10,model.getTotalApplyPrice());
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,11,model.getReducingTaxExemptionsId() + " " + model.getReducingTaxExemptions());
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,12,destinationCountryId + " " + descrtionCountry);
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,13,"中国");
        POIExportExcelUtil.updateCellValue(sheet,rowLine+i,14,model.getBrand());
        if(!StringUtils.isEmpty(model.getDeclarationElement()))
        {
            if(model.getDeclarationElement().contains("|"))
            {
                String[] elements = model.getDeclarationElement().split("\\|");

                POIExportExcelUtil.updateCellValue(sheet,rowLine+i,15,elements[0]);
                POIExportExcelUtil.updateCellValue(sheet,rowLine+i,16,elements[1]);
            }
            else
                POIExportExcelUtil.updateCellValue(sheet,rowLine+i,15,model.getDeclarationElement());
        }
    }
}
